package tsli.acm.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import tsli.acm.database.Database;
import tsli.acm.model.TempAl;

public class TempAlDAO {

	private Database db;

	public TempAlDAO(Database db) {
		this.db = db;
	}

	public boolean insert(TempAl tempAl) {
		boolean act = false;
		try {
			String sql = "insert into tsli_temp_al ( TEMP_ID,PE_NO_COL , PE_NO_SM , PO_CLOS_YM  , AFYP , ARYP2 , PC_AL , PO_NO , RIDER_CD )"
					+ "VALUES ( ? , ? , ? , ? , ? , ? , ?, ?, ?) ";
			this.db.insert(sql, tempAl.getVaTempId(), tempAl.getVaPeNoCol() , tempAl.getVaPeNoSm(),
					tempAl.getVaPoClosYm(), tempAl.getVaAfyp(),
					tempAl.getVaAryp2(), tempAl.getVaPcAl(),tempAl.getVaPoNo(),tempAl.getVaRIderCd());
			act = true;
		} catch (Exception ex) {
			ex.printStackTrace();
			act = false;
		}
		return act;
	}
	
	public boolean deleteFreeLooks(String closYM,String poClosYM){
		boolean result = false;
		
		try { 
		String vaSql = ""+
				"		DELETE tsli_temp_al										 	                	\n"+
				"		WHERE (PO_CLOS_YM,PE_NO_COL,PE_NO_SM,PO_NO,RIDER_CD) IN 			 	    	\n"+
				"		(                                                        	                	\n"+
				"	SELECT p.CLOS_YM as PO_CLOS_YM,p.PE_NO_COL,p.PE_NO_SM,p.PO_NO,p.RIDER_CD			\n "+
				"	FROM tsli_ga_commission_negative n                                                   \n"+
				"	LEFT JOIN tsli_ga_commission_positive p                                              \n"+
				"		ON n.po_no =  p.po_no                                                            \n"+
				"	WHERE  n.PE_NO_SM in                                                                 \n"+
				"	(SELECT PE_NO  FROM tsli_ga_agent_structure                                          \n"+  
				"	WHERE dty_cd = 'S10240203'                                                           \n"+
				"	and LCNS_RNWL_YMD IS NOT NULL)                                                       \n"+
				"	and p.dty_cd IN ('S10240206','S10240203','S10240205')                                \n"+
				"	--Not count in the future                                                            \n"+
				"	AND n.PO_NO  NOT IN                                                                  \n"+
				"	(SELECT a.PO_NO                                                                      \n"+
				"	FROM tsli_ga_commission_positive a                                                   \n"+
				"	WHERE a.clos_ym > ?  ) 	                                                     		\n"+
				"	--Not in freelook temp because it already count                                      \n"+
				"	AND (p.CLOS_YM,p.PE_NO_COL,p.PE_NO_SM,p.PO_NO,p.RIDER_CD) NOT IN		             \n"+
				"	(                                                                                    \n"+
				"	select l.PO_CLOS_YM as CLOS_YM,l.PE_NO_COL,l.PE_NO_SM,l.PO_NO,l.RIDER_CD             \n"+
				"	from tsli_temp_al_freelook l                                                         \n"+			
				"		)                                                        	                	\n"+
				"	)	AND PO_CLOS_YM = ?	                        						      		\n";
		
//		System.out.println("deleteFreeLooks "+ vaSql);
		this.db.update(vaSql,closYM,poClosYM);
		System.out.println("infor : remove freelook in tempAL complete.");
		
		}catch(Exception ex){
			System.out.println("ERROR : "+ex);
			return result;
		}
		return true;
		
		
	}

	/**
	 * this sum PC AL , AG
	 * 
	 * @param poClosYm
	 * @return
	 */
	public List<Map<String, Object>> sumPcAlAg(String poClosYm) {
		String vaSql = " SELECT AL.PE_NO_SM , AL.PE_NO_COL , SUM(AL.PC_AL + AG.PC_AG) AS SUM_PC FROM TSLI_TEMP_AL AL "
				+ " INNER JOIN TSLI_TEMP_AG AG ON AL.PE_NO_SOLI = AG.PE_NO_SM "
				+ " WHERE AL.PO_CLOS_YM = '"
				+ poClosYm
				+ "'"
				+ " GROUP BY AL.PE_NO_SM , AL.PE_NO_COL ";
		return this.db.queryList(vaSql);
	}

	/**
	 * insert temp value PC of AL
	 * 
	 * @param db
	 * @param vaClosYm
	 * @return
	 */
	public boolean calPcOfAL(String vaClosYm) {
		boolean act = false;
		try {
			this.removePcOfAl(vaClosYm);
			GAAgentStructureDAO gaAgentStructureDAO = new GAAgentStructureDAO(db);
			List<Map<String, Object>> listsPcOfAL = gaAgentStructureDAO.getPcOfAL(vaClosYm);
			for (int i = 0; i < listsPcOfAL.size(); i++) {
				try{
				Map<String, Object> item = listsPcOfAL.get(i);
				TempAl tempAl = new TempAl();
				tempAl.setVaRIderCd(item.get("RIDER_CD").toString());
				tempAl.setVaPoNo(item.get("PO_NO").toString());
				tempAl.setVaTempId(Integer.parseInt(item.get("TEMP_ID").toString()));
				tempAl.setVaPeNoSm(item.get("PE_NO_SM").toString());
				tempAl.setVaPeNoCol(item.get("PE_NO_COL").toString());
				tempAl.setVaPoClosYm(item.get("PO_CLOS_YM").toString());
				tempAl.setVaAfyp(Double.parseDouble(item.get("AFYP").toString()));
				tempAl.setVaAryp2(Double.parseDouble(item.get("ARYP2").toString()));
				tempAl.setVaPcAl(Double.parseDouble(item.get("PC_AL").toString()));
//				System.out.println("Info : summary PC for AL");
				TempAlDAO tempAlDAO = new TempAlDAO(db);
				tempAlDAO.insert(tempAl);
				}
				catch(Exception e){
					System.out.println("ERROR : "+e);
				}
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return act;
	}
	
	/**
	 * remove data 
	 * @param vaCloseYm
	 * @return
	 */
	private int removePcOfAl(String vaCloseYm){
		int act = 0;
		try { 
			String vaSQL =  "DELETE FROM TSLI_TEMP_AL WHERE  PO_CLOS_YM = ? ";
			this.db.update(vaSQL, vaCloseYm);
			act = 1;
			System.out.println("infor : remove temp complete.");
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return act;
	}
	
	/** 
	 * this sum PC AL
	 * @param closYm
	 * @return
	 */
	public List<Map<String , Object>> getPcAll(String closYm){ 
		List<Map<String , Object>> result = new ArrayList<Map<String,Object>>();
		try { 
			String vaSql = " SELECT  PE_NO_SM  , PO_CLOS_YM , SUM(PC_AL) AS PC_AL FROM TSLI_TEMP_AL " +
						   " GROUP BY PE_NO_SM ,  PO_CLOS_YM " + 
						   " HAVING PO_CLOS_YM = ? ";
//			System.out.println("Info : sum  pc all " + vaSql);
			result =  this.db.queryList(vaSql, closYm);
		} catch(Exception ex){ 
			ex.printStackTrace();
		}
		return result;
	}

}